package com.redstar.study;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DataBaseHelper extends SQLiteOpenHelper{

	//The Android's default system path of your application database.
	private static String DB_PATH = "/data/data/com.redstar.study/databases/";
	private static String DB_NAME = "data";
	private SQLiteDatabase myDataBase; 
	private final Context myContext;

	/**
	 * Constructor
	 * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
	 * @param context
	 */
	public DataBaseHelper(Context context) {

		super(context, DB_NAME, null, 3);
		this.myContext = context;
	}	

	/**
	 * Creates a empty database on the system and rewrites it with your own database.
	 * */
	public void createDataBase() throws IOException{

		boolean dbExist = checkDataBase();

		if(dbExist){
			//do nothing - database already exist
		}else{
			//By calling this method and empty database will be created into the default system path
			//of your application so we are gonna be able to overwrite that database with our database.
			this.getReadableDatabase();

			try {
				copyDataBase();
			}
			catch (IOException e) {
				throw new Error("Error copying database");
			}
		}
	}

	/**
	 * Check if the database already exist to avoid re-copying the file each time you open the application.
	 * @return true if it exists, false if it doesn't
	 */
	private boolean checkDataBase(){

		SQLiteDatabase checkDB = null;

		try{
			String myPath = DB_PATH + DB_NAME;
			checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

		}catch(SQLiteException e){
			//database does't exist yet.
		}
		if(checkDB != null){
			checkDB.close();
		}

		return checkDB != null ? true : false;
	}

	/**
	 * Copies your database from your local assets-folder to the just created empty database in the
	 * system folder, from where it can be accessed and handled.
	 * This is done by transfering bytestream.
	 * */
	private void copyDataBase() throws IOException{

		//Open your local db as the input stream
		InputStream myInput = myContext.getAssets().open(DB_NAME);

		// Path to the just created empty db
		String outFileName = DB_PATH + DB_NAME;

		//Open the empty db as the output stream
		OutputStream myOutput = new FileOutputStream(outFileName);

		//transfer bytes from the inputfile to the outputfile
		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer))>0){
			myOutput.write(buffer, 0, length);
		}

		//Close the streams
		myOutput.flush();
		myOutput.close();
		myInput.close();

	}

	public void openDataBase() throws SQLException{

		//Open the database
		String myPath = DB_PATH + DB_NAME;
		myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

	}

	@Override
	public synchronized void close() {

		if(myDataBase != null)
			myDataBase.close();

		super.close();

	}

	@Override
	public void onCreate(SQLiteDatabase db) {

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		try {
			copyDataBase();
		}
		catch (IOException e) {
			throw new Error("Error copying database");
		}
	}

	// Add your public helper methods to access and get content from the database.
	// You could return cursors by doing "return myDataBase.query(....)" so it'd be easy
	// to you to create adapters for your views.

	/**
	 * Return all words
	 * @param sKIP_LEARNT 
	 * @return
	 */
	public List<Word> getAllVocab(boolean SKIP_LEARNT){
		
		SQLiteDatabase db=this.getReadableDatabase();
		Cursor c = db.rawQuery("select * from Vocabulary", null);

		return createWordList(c, SKIP_LEARNT);
	}
	/**
	 * Return all words based on a search string
	 * @return
	 */
	public List<Word> getVocabSearch(String query){
		SQLiteDatabase db=this.getReadableDatabase();
		Cursor c=db.rawQuery("select * FROM Vocabulary WHERE Definitions LIKE '%"+query+"%' " +
														"OR Type LIKE '%"+query+"%' " +
														"OR Kana LIKE '%"+query+"%' " +
														"OR Romanji LIKE '%"+query+"%' " +
														"OR Kanji LIKE '%"+query+"%' ", null);
		
		return createWordList(c, false);
	}

	public List<Word> getVocabWithOptions(String filter, String order, boolean skip_learnt) {
		// TODO Auto-generated method stub
		SQLiteDatabase db=this.getReadableDatabase();
		Cursor c=db.rawQuery("select * FROM Vocabulary WHERE Type LIKE '%"+filter+"%' ORDER BY  " + order , null);
	
		return createWordList(c, skip_learnt);
	}

	private List<Word> createWordList(Cursor c, boolean skip_learnt) {
		// TODO Auto-generated method stub
		List<Word> ListOfWords = new ArrayList<Word>();
		
		while(c.moveToNext()){
			if(skip_learnt){
				if(c.getDouble(c.getColumnIndex("Rating"))!=5){
					ListOfWords.add(new Word(c.getInt(c.getColumnIndex("_id")),
							 c.getString(c.getColumnIndex("Kanji")),
							 c.getString(c.getColumnIndex("Kana")) , 
							 c.getString(c.getColumnIndex("Romanji")),
							 c.getString(c.getColumnIndex("Type")),
							 c.getString(c.getColumnIndex("Definitions")),
							 c.getDouble(c.getColumnIndex("Rating"))));					
				}
			}else{
				ListOfWords.add(new Word(c.getInt(c.getColumnIndex("_id")),
						 c.getString(c.getColumnIndex("Kanji")),
						 c.getString(c.getColumnIndex("Kana")) , 
						 c.getString(c.getColumnIndex("Romanji")),
						 c.getString(c.getColumnIndex("Type")),
						 c.getString(c.getColumnIndex("Definitions")),
						 c.getDouble(c.getColumnIndex("Rating"))));
			}
		}
		c.close();
		return ListOfWords;
		
	}

	public void setRating(Word tempWord, double newRating) {
		// TODO Auto-generated method stub
		SQLiteDatabase db=this.getReadableDatabase();
		/*db.execSQL("UPDATE Vocabulary SET Rating='"+newRating+"' WHERE Definitions='"+tempWord.getDefinition()+"' " +
					"AND Type='"+tempWord.getType()+"' " +
					"AND Kana='"+tempWord.getKana()+"' " +
					"AND Kanji='"+tempWord.getKanji()+"' ");*/
		db.execSQL("UPDATE Vocabulary SET Rating='"+newRating+"' WHERE _id='"+tempWord.getId()+"' ");
		db.close();
		Log.e("database","udated rating");

	}
}